/*
 * Created on 2005-10-20
 *
 */
package cn.com.pxto.dao.pagesupport.hibernate;

/**
 * @author Gauss Cheung
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.Session;
import net.sf.hibernate.SessionFactory;
import net.sf.hibernate.type.Type;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate.HibernateCallback;
import org.springframework.orm.hibernate.HibernateTemplate;

import cn.com.pxto.commons.pagesupport.PageInfo;

public class HibernatePageTemplate extends HibernateTemplate {
	// override the Constructor from the HibernateTemplate
	public HibernatePageTemplate() {
		super();
	}

	public HibernatePageTemplate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}

	public HibernatePageTemplate(SessionFactory sessionFactory,
			boolean allowCreate) {
		super(sessionFactory, allowCreate);
	}

	/*
	 * Translate the Select HQL to Count HQL which to count the result amount.
	 */
	private String initCountHQL(final String queryString) {
		// replace the string to 'Count(*) which between 'select' and 'from'.
		int selectLocation = queryString.toLowerCase().indexOf("select") + 6;
		int fromLocation = queryString.toLowerCase().indexOf("from");
		StringBuffer bufferString = new StringBuffer(queryString);
		bufferString.replace(selectLocation, fromLocation, " Count(*) ");
		String HQLString = bufferString.toString();
		// remove order by and the follow string from hsql.
		// Because the 'Count(*)' function can not support 'order by' in all
		// Database product.
		int orderbyLocation = HQLString.toLowerCase().lastIndexOf("order by");

		return HQLString.substring(0, orderbyLocation);
	}

	/*
	 * Get the records amount
	 */
	private void initPageInfo(final String queryString, final Object[] values,
			final Type[] types, PageInfo pageInfo) throws DataAccessException {
		// count the record
		String HQLString = initCountHQL(queryString);
		List list= this.find(HQLString, values, types);
		int totalResult;
		//contains 'group by' and have no sub query
		if(queryString.indexOf("group by")>0 && HQLString.lastIndexOf("select")==0){
			//because 'count(*)' statement with 'group by' return
			// a list with record count for every group,the totalResult 
			//is just the list size.
			totalResult= list.size();
		}else if (list.size()==0) {
			throw new IllegalStateException("Count record failed! HSQL="+HQLString);
		} else {
			totalResult = ((Integer)list.get(0)).intValue();
		}
		// calulate the page info
		pageInfo.setTotalResult(totalResult);
		pageInfo.calculate();
	}

	/*
	 * search Datas support page HQL string must include "select" and "form",
	 * because get datas count must use this. -------------------------
	 */
	// public final List pagedFind(final String queryString,
	// final Object[] values, final Type[] types, final int pageSize,
	// final int pageNo) throws DataAccessException {
	public final List pagedFind(final String queryString,
			final Object[] values, final Type[] types, final PageInfo pageInfo)
			throws DataAccessException {
		if (values != null && types != null && values.length != types.length) {
			throw new IllegalArgumentException(
					"Length of values array must match length of types array");
		}
		// interface HibernateCallback
		return (List) execute(new HibernateCallback() {
			// implment the doInHibernate method
			public Object doInHibernate(Session session)
					throws HibernateException {
				Query queryObject = session.createQuery(queryString);
				prepareQuery(queryObject);
				initPageInfo(queryString, values, types, pageInfo);
				int pageSize = pageInfo.getPageSize();
				queryObject.setMaxResults(pageInfo.getPageSize());
				int firstResult = pageInfo.getBeginResult();
				queryObject.setFirstResult(pageInfo.getBeginResult());
				if (values != null) {
					for (int i = 0; i < values.length; i++) {
						if (types != null && types[i] != null) {
							queryObject.setParameter(i, values[i], types[i]);
						} else {
							queryObject.setParameter(i, values[i]);
						}
					}
				}
				return queryObject.list();
			}
		}, true);
	}

	public final List pagedFind(final String queryString, PageInfo pageInfo)
			throws DataAccessException {
		return pagedFind(queryString, (Object[]) null, (Type[]) null, pageInfo);
	}

	public final List pagedFind(final String queryString, final Object value,
			PageInfo pageInfo) throws DataAccessException {
		return pagedFind(queryString, new Object[] { value }, (Type[]) null,
				pageInfo);

	}

	public final List pagedFind(final String queryString,
			final Object[] values, PageInfo pageInfo)
			throws DataAccessException {
		return pagedFind(queryString, values, (Type[]) null, pageInfo);

	}

	public final List pagedFind(final String queryString, final Object value,
			final Type type, PageInfo pageInfo) throws DataAccessException {
		return pagedFind(queryString, new Object[] { value },
				new Type[] { type }, pageInfo);
	}

	/*
	 * --------------------------
	 */
	public int executeSQL(String queryString, final Object[] values)
			throws HibernateException, SQLException {
		Connection con = getSession().connection();
		PreparedStatement pstmt = con.prepareStatement(queryString);
		for (int i = 0; i < values.length; i++) {
			pstmt.setObject(i + 1, values[i]);
		}
		pstmt.execute();
		return pstmt.getUpdateCount();
	}
}
